* Statistics for Tables 5 and A.7
* Final goods import share and intermediate input import share for Mexico from INEGI data.

* Final goods
clear
import delimited "../Database/INEGI/ConsumoPrivadoNacionalBienes"
drop in 1/5
ren v2 Cdom
destring Cdom, replace
drop in 111/112
replace v1="2018/01" if v1=="2018/01 /p1"
gen year = substr(v1,1,4)
gen quarter = substr(v1,6,7)
gen period = quarterly(v1,"YQ")
sort period
save "temp/Cdom_temp.dta", replace

import delimited "../Database/INEGI/ConsumoPrivadoImportadoBienes", clear
drop in 1/5
ren v2 Cimp
destring Cimp, replace
drop in 111/112
replace v1="2018/01" if v1=="2018/01 /p1"
gen year = substr(v1,1,4)
gen quarter = substr(v1,6,7)
gen period = quarterly(v1,"YQ")
sort period
save "temp/Cimp_temp.dta", replace
merge 1:1 period using "temp/Cdom_temp.dta"
drop _merge
drop v1
order year quarter period, first
collapse (sum) Cdom Cimp, by(year)
gen importshareC = Cimp / (Cimp + Cdom) * 100
destring year, replace
keep if year>=1993 & year<=1997
export excel year importshareC using "../../../Output/Table5_and_A7", first(var) sh("Consumption") replace

* Intermediates
clear
import delimited "../Database/INEGI/DemandaIntermedia"
drop in 1/5
ren v2 Intermediates
destring Intermediates, replace
drop in 111/112
replace v1="2018/01" if v1=="2018/01 /p1"
gen year = substr(v1,1,4)
gen quarter = substr(v1,6,7)
gen period = quarterly(v1,"YQ")
sort period
save "temp/Intermediates_temp.dta", replace
import delimited "../Database/INEGI/ImportacionesIntermedios", clear
drop in 1/5
ren v2 IntermediatesImports
destring IntermediatesImports, replace
drop in 111/112
replace v1="2018/01" if v1=="2018/01 /p1"
gen year = substr(v1,1,4)
gen quarter = substr(v1,6,7)
gen period = quarterly(v1,"YQ")
sort period
merge 1:1 period using "temp/Intermediates_temp.dta"
drop _merge
drop v1
order year quarter period, first
collapse (sum) IntermediatesImports Intermediates, by(year)
gen importshare = IntermediatesImports / Intermediates * 100
destring year, replace
keep if year>=1993 & year<=1997
export excel year importshare using "../../../Output/Table5_and_A7", first(var) sh("Intermediates")
